﻿Imports System.Data.SqlClient
Imports Control_Vuelos_BE


Public Class ManifiestoADO
    Dim MiConexion As New ConexionADO
    Dim cnx As New SqlConnection(MiConexion.GetCnx)
    Dim cmd As New SqlCommand

    Public Function ListarManifiesto(ByVal p_CodManifiesto As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Listar_Manifiesto", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodViaje", SqlDbType.Char, 12)
                    cmd.Parameters("@p_CodViaje").Value = p_CodManifiesto.Trim
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Manifiesto")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ManifiestoADO (ListarManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_AgregarPasajero(ByVal p_D_Manifiesto As D_ManifiestoBE) As Boolean
        Try
            Dim sInstruccion As String = ""
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim oTran As SqlTransaction
            Using cn
                cn.Open()
                oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                sInstruccion = "INSERT INTO dbo.D_Manifiesto(CodReserva,NroDocumentoId,CodViaje,FlgResponsable,NomPasajero,NacionalidadPasajero,ApmPasajero,ObsManifiesto,AppPasajero,CodAnalisis,CodCentroCosto,PctCentroCosto,CodCentroCosto2,FlgLock,PctCentroCosto2) " & _
                    " VALUES (@CodReserva,@NroDocumentoId,@CodViaje,@FlgResponsable,@NomPasajero,@NacionalidadPasajero,@ApmPasajero,@ObsManifiesto,@AppPasajero,@CodAnalisis,@CodCentroCosto,@PctCentroCosto,@CodCentroCosto2,@FlgLock,@PctCentroCosto2) "
                Dim cmd As New SqlCommand(sInstruccion, cn)
                Using cmd
                    cmd.Transaction = oTran
                    cmd.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                    cmd.Parameters.AddWithValue("@NroDocumentoId", p_D_Manifiesto.NroDocumentoId)
                    cmd.Parameters.AddWithValue("@CodViaje", p_D_Manifiesto.CodViaje)
                    cmd.Parameters.AddWithValue("@FlgResponsable", p_D_Manifiesto.FlgResponsable)
                    cmd.Parameters.AddWithValue("@NomPasajero", p_D_Manifiesto.NomPasajero)
                    cmd.Parameters.AddWithValue("@NacionalidadPasajero", p_D_Manifiesto.NacionalidadPasajero)
                    cmd.Parameters.AddWithValue("@ApmPasajero", p_D_Manifiesto.ApmPasajero)
                    cmd.Parameters.AddWithValue("@ObsManifiesto", p_D_Manifiesto.ObsManifiesto)
                    cmd.Parameters.AddWithValue("@AppPasajero", p_D_Manifiesto.AppPasajero)
                    cmd.Parameters.AddWithValue("@CodAnalisis", p_D_Manifiesto.CodAnalisis)
                    cmd.Parameters.AddWithValue("@CodCentroCosto", p_D_Manifiesto.CodCentroCosto)
                    cmd.Parameters.AddWithValue("@PctCentroCosto", p_D_Manifiesto.PctCentroCosto)
                    cmd.Parameters.AddWithValue("@CodCentroCosto2", p_D_Manifiesto.CodCentroCosto2)
                    cmd.Parameters.AddWithValue("@PctCentroCosto2", p_D_Manifiesto.PctCentroCosto2)
                    cmd.Parameters.AddWithValue("@FlgLock", p_D_Manifiesto.FlgLock)
                    cmd.ExecuteNonQuery()
                End Using
                Dim sUpdReserva As String = "UPDATE V_Reserva SET EstadoReserva=3 WHERE CodReserva = @CodReserva"
                Dim cmd1 As New SqlCommand(sUpdReserva, cn)
                Using cmd1
                    cmd1.Transaction = oTran
                    cmd1.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)                    
                    cmd1.ExecuteNonQuery()
                End Using
            End Using
            oTran.Commit()
            Return True
        Catch myException As Exception
            MsgBox("ManifiestoADO (pr_AgregarPasajeroManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Function pr_AgregarPasajeroManifiesto(ByVal p_C_Manifiesto As C_ManifiestoBE, ByVal p_D_Manifiesto As D_ManifiestoBE) As Boolean
        Try
            Try
                Dim sInstruccion As String = ""
                Dim sInstruccion1 As String = ""
                sInstruccion = "SELECT count(*) FROM dbo.C_Manifiesto WHERE CodViaje = @CodViaje"
                Dim oTran As SqlTransaction
                Dim cn As New SqlConnection(MiConexion.GetCnx)
                Using cn
                    cn.Open()
                    oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                    Dim cmd As New SqlCommand(sInstruccion, cn)
                    Using cmd
                        cmd.CommandType = CommandType.Text
                        cmd.Parameters.Add("@CodViaje", SqlDbType.Char, 12)
                        cmd.Parameters("@CodViaje").Value = p_C_Manifiesto.CodViaje.Trim
                        cmd.Transaction = oTran
                        Dim t As Integer = CInt(cmd.ExecuteScalar())
                        If t > 0 Then                            
                        Else
                            sInstruccion1 = "INSERT INTO dbo.C_Manifiesto(CodViaje, FchViaje, CodLugarOrigen, CodLugarDestino, NomAeropuerto, CodEmpresa, ObsCabeceraManifiesto, EstadoManifiesto, FlgValija, HraViaje,Contacto) " & _
                                            "VALUES (@CodViaje, @FchViaje, @CodLugarOrigen, @CodLugarDestino, @NomAeropuerto,@CodEmpresa,@ObsCabeceraManifiesto,@EstadoManifiesto,@FlgValija,@HraViaje,@Contacto)"
                            Dim cmd1 As New SqlCommand(sInstruccion1, cn)
                            Using cmd1
                                cmd1.Parameters.AddWithValue("@CodViaje", p_C_Manifiesto.CodViaje)
                                cmd1.Parameters.AddWithValue("@FchViaje", p_C_Manifiesto.FchViaje)
                                cmd1.Parameters.AddWithValue("@CodLugarOrigen", p_C_Manifiesto.CodLugarOrigen)
                                cmd1.Parameters.AddWithValue("@CodLugarDestino", p_C_Manifiesto.CodLugarDestino)
                                cmd1.Parameters.AddWithValue("@NomAeropuerto", p_C_Manifiesto.NomAeropuerto)
                                cmd1.Parameters.AddWithValue("@CodEmpresa", p_C_Manifiesto.CodEmpresa)
                                cmd1.Parameters.AddWithValue("@ObsCabeceraManifiesto", p_C_Manifiesto.ObsCabeceraManifiesto)
                                cmd1.Parameters.AddWithValue("@EstadoManifiesto", p_C_Manifiesto.EstadoManifiesto)
                                cmd1.Parameters.AddWithValue("@FlgValija", p_C_Manifiesto.FlgValija)
                                cmd1.Parameters.AddWithValue("@HraViaje", p_C_Manifiesto.HraViaje)
                                cmd1.Parameters.AddWithValue("@Contacto", p_C_Manifiesto.Contacto)
                                cmd1.Parameters.AddWithValue("@ObsCabecera", p_C_Manifiesto.ObsCabecera)
                                cmd1.Parameters.AddWithValue("@TelContacto", p_C_Manifiesto.TelContacto)
                                cmd1.Transaction = oTran
                                cmd1.ExecuteNonQuery()
                            End Using
                        End If
                        sInstruccion = "INSERT INTO dbo.D_Manifiesto(CodReserva,NroDocumentoId,CodViaje,FlgResponsable,NomPasajero,NacionalidadPasajero,ApmPasajero,ObsManifiesto,AppPasajero,CodAnalisis,CodCentroCosto,PctCentroCosto,CodCentroCosto2,FlgLock,PctCentroCosto2) " & _
                        " VALUES (@CodReserva,@NroDocumentoId,@CodViaje,@FlgResponsable,@NomPasajero,@NacionalidadPasajero,@ApmPasajero,@ObsManifiesto,@AppPasajero,@CodAnalisis,@CodCentroCosto,@PctCentroCosto,@CodCentroCosto2,@FlgLock,@PctCentroCosto2) "
                        Dim cmd2 As New SqlCommand(sInstruccion, cn)
                        Using cmd2
                            cmd2.Transaction = oTran
                            cmd2.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                            cmd2.Parameters.AddWithValue("@NroDocumentoId", p_D_Manifiesto.NroDocumentoId)
                            cmd2.Parameters.AddWithValue("@CodViaje", p_D_Manifiesto.CodViaje)
                            cmd2.Parameters.AddWithValue("@FlgResponsable", p_D_Manifiesto.FlgResponsable)
                            cmd2.Parameters.AddWithValue("@NomPasajero", p_D_Manifiesto.NomPasajero)
                            cmd2.Parameters.AddWithValue("@NacionalidadPasajero", p_D_Manifiesto.NacionalidadPasajero)
                            cmd2.Parameters.AddWithValue("@ApmPasajero", p_D_Manifiesto.ApmPasajero)
                            cmd2.Parameters.AddWithValue("@ObsManifiesto", p_D_Manifiesto.ObsManifiesto)
                            cmd2.Parameters.AddWithValue("@AppPasajero", p_D_Manifiesto.AppPasajero)
                            cmd2.Parameters.AddWithValue("@CodAnalisis", p_D_Manifiesto.CodAnalisis)
                            cmd2.Parameters.AddWithValue("@CodCentroCosto", p_D_Manifiesto.CodCentroCosto)
                            cmd2.Parameters.AddWithValue("@PctCentroCosto", p_D_Manifiesto.PctCentroCosto)
                            cmd2.Parameters.AddWithValue("@CodCentroCosto2", p_D_Manifiesto.CodCentroCosto2)
                            cmd2.Parameters.AddWithValue("@PctCentroCosto2", p_D_Manifiesto.PctCentroCosto2)
                            cmd2.Parameters.AddWithValue("@FlgLock", p_D_Manifiesto.FlgLock)
                            cmd2.ExecuteNonQuery()
                        End Using

                        Dim sUpdReserva As String = "UPDATE V_Reserva SET EstadoReserva=3, FchViaje = @FchViaje WHERE CodReserva = @CodReserva"

                        Dim cmd4 As New SqlCommand(sUpdReserva, cn)
                        Using cmd4
                            cmd4.Transaction = oTran
                            cmd4.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                            cmd4.Parameters.AddWithValue("@FchViaje", p_C_Manifiesto.FchViaje)
                            cmd4.ExecuteNonQuery()
                        End Using

                    End Using
                    oTran.Commit()
                    Return True
                End Using
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_AgregarPasajeroManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                '                oTran.Rollback()
                Return False
            End Try
        Catch myException As Exception
            MsgBox("ManifiestoADO (pr_AgregarPasajeroManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Function pr_UpdateDetalleManifiestoResponsable(ByVal p_CodViaje As String, ByVal p_CodReserva As String, ByVal p_FlgResponsable As Byte) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim mySqlCommand As SqlCommand
            Dim sInstruccion As String = " Update dbo.D_Manifiesto Set FlgResponsable = @FlgResponsable  WHERE CodViaje = @CodViaje AND CodReserva = @CodReserva "
            Try
                mySqlCommand = New SqlCommand(sInstruccion, cn)
                mySqlCommand.Transaction = oTran
                mySqlCommand.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                mySqlCommand.Parameters.AddWithValue("@CodReserva", p_CodReserva.Trim)
                mySqlCommand.Parameters.AddWithValue("@FlgResponsable", p_FlgResponsable)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_UpdateDetalleManifiestoResponsable) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_UpdateDetalleManifiestoLock(ByVal p_CodViaje As String, ByVal p_CodReserva As String, ByVal p_FlgLock As Int32) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim mySqlCommand As SqlCommand
            Dim sInstruccion As String = " Update dbo.D_Manifiesto Set FlgLock = @FlgLock  WHERE CodViaje = @CodViaje AND CodReserva = @CodReserva "
            Try
                mySqlCommand = New SqlCommand(sInstruccion, cn)
                mySqlCommand.Transaction = oTran
                mySqlCommand.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                mySqlCommand.Parameters.AddWithValue("@CodReserva", p_CodReserva.Trim)
                mySqlCommand.Parameters.AddWithValue("@FlgLock", p_FlgLock)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_UpdateDetalleManifiestoLock) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_RemoverPasajeroManifiesto(ByVal p_D_Manifiesto As D_ManifiestoBE) As Boolean       
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction(IsolationLevel.Serializable)
            Dim sInstruccion As String = "DELETE FROM dbo.D_Manifiesto " & _
                " WHERE CodViaje = @CodViaje AND CodReserva = @CodReserva  "
            Dim cmd2 As New SqlCommand(sInstruccion, cn)
            cmd2.Transaction = oTran
            cmd2.Parameters.AddWithValue("@CodViaje", p_D_Manifiesto.CodViaje)
            cmd2.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
            Try
                cmd2.ExecuteNonQuery()
                Dim sUpdReserva As String = "UPDATE V_Reserva SET EstadoReserva=2 WHERE CodReserva = @CodReserva"
                Dim cmd4 As New SqlCommand(sUpdReserva, cn)
                cmd4.Transaction = oTran
                cmd4.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                cmd4.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_RemoverPasajeroManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_RecuperarCabecera(ByVal p_CodViaje As String) As C_ManifiestoBE
        Dim _C_ManifiestoBE As New C_ManifiestoBE
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim sInstruccion As String = "SELECT * FROM dbo.C_Manifiesto " & _
                " WHERE CodViaje = @CodViaje  "
            Dim cmd2 As New SqlCommand(sInstruccion, cn)
            cmd2.Parameters.AddWithValue("@CodViaje", p_CodViaje)
            Try
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd2)
                Dim dt As DataTable = New DataTable("Cabecera")
                da.Fill(dt)
                Dim drCurrent As DataRow
                For Each drCurrent In dt.Rows
                    _C_ManifiestoBE.CodViaje = drCurrent("CodViaje").ToString
                    _C_ManifiestoBE.FchViaje = drCurrent("FchViaje")
                    _C_ManifiestoBE.HraViaje = drCurrent("HraViaje")
                    _C_ManifiestoBE.Contacto = drCurrent("Contacto")
                    _C_ManifiestoBE.NomAeropuerto = drCurrent("NomAeropuerto")
                    _C_ManifiestoBE.ObsCabeceraManifiesto = drCurrent("ObsCabeceraManifiesto")
                    _C_ManifiestoBE.CodLugarOrigen = drCurrent("CodLugarOrigen")
                    _C_ManifiestoBE.CodLugarDestino = drCurrent("CodLugarDestino")
                    _C_ManifiestoBE.FlgValija = drCurrent("FlgValija")
                    _C_ManifiestoBE.EstadoManifiesto = drCurrent("EstadoManifiesto")
                    _C_ManifiestoBE.CodEmpresa = drCurrent("CodEmpresa")
                    If IsDBNull(drCurrent("ObsCabecera")) Then
                        _C_ManifiestoBE.ObsCabecera = ""
                    Else
                        _C_ManifiestoBE.ObsCabecera = drCurrent("ObsCabecera")
                    End If
                    If IsDBNull(drCurrent("TelContacto")) Then
                        _C_ManifiestoBE.TelContacto = ""
                    Else
                        _C_ManifiestoBE.TelContacto = drCurrent("TelContacto")
                    End If

                    If IsDBNull(drCurrent("NroValija")) Then
                        _C_ManifiestoBE.NroValija = ""
                    Else
                        _C_ManifiestoBE.NroValija = drCurrent("NroValija")
                    End If

                    If IsDBNull(drCurrent("FlgProducto")) Then
                        _C_ManifiestoBE.FlgProducto = False
                    Else
                        _C_ManifiestoBE.FlgProducto = drCurrent("FlgProducto")
                    End If

                    If IsDBNull(drCurrent("FlgProvisionado")) Then
                        _C_ManifiestoBE.FlgProvisionado = False
                    Else
                        _C_ManifiestoBE.FlgProvisionado = drCurrent("FlgProvisionado")
                    End If
                Next
                Return _C_ManifiestoBE
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_RecuperarCabecera) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function


    Public Function pr_RecuperarDetalle(ByVal p_CodViaje As String, ByVal p_CodReserva As Integer) As D_ManifiestoBE
        Dim _D_ManifiestoBE As New D_ManifiestoBE
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim sInstruccion As String = "SELECT * FROM dbo.D_Manifiesto " & _
                " WHERE CodViaje = @CodViaje AND CodReserva = @CodReserva "
            Dim cmd2 As New SqlCommand(sInstruccion, cn)
            cmd2.Parameters.AddWithValue("@CodViaje", p_CodViaje)
            cmd2.Parameters.AddWithValue("@CodReserva", p_CodReserva)
            Try
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd2)
                Dim dt As DataTable = New DataTable("Detalle")
                da.Fill(dt)
                Dim drCurrent As DataRow
                For Each drCurrent In dt.Rows
                    _D_ManifiestoBE.CodViaje = drCurrent("CodViaje").ToString
                    _D_ManifiestoBE.CodReserva = drCurrent("CodReserva")
                    _D_ManifiestoBE.NroItem = IIf(IsDBNull(drCurrent("NroItem")), "", drCurrent("NroItem"))
                    _D_ManifiestoBE.NroDocumentoId = drCurrent("NroDocumentoId")
                    _D_ManifiestoBE.AppPasajero = drCurrent("AppPasajero")
                    _D_ManifiestoBE.ApmPasajero = drCurrent("ApmPasajero")
                    _D_ManifiestoBE.NomPasajero = drCurrent("NomPasajero")
                    _D_ManifiestoBE.NacionalidadPasajero = drCurrent("NacionalidadPasajero")
                    _D_ManifiestoBE.CodAnalisis = drCurrent("CodAnalisis")
                    _D_ManifiestoBE.CodComunidad = IIf(IsDBNull(drCurrent("CodComunidad")), "", drCurrent("CodComunidad"))
                    _D_ManifiestoBE.CodCentroCosto = drCurrent("CodCentroCosto")
                    _D_ManifiestoBE.PctCentroCosto = drCurrent("PctCentroCosto")
                    _D_ManifiestoBE.CodCentroCosto2 = drCurrent("CodCentroCosto2")
                    _D_ManifiestoBE.PctCentroCosto2 = drCurrent("PctCentroCosto2")
                    _D_ManifiestoBE.TipPorExcepcion = IIf(IsDBNull(drCurrent("TipPorExepcion")), "", drCurrent("TipPorExepcion"))
                    _D_ManifiestoBE.ObsManifiesto = drCurrent("ObsManifiesto")
                    _D_ManifiestoBE.MonPasaje = IIf(IsDBNull(drCurrent("MonPasaje")), 0.0, drCurrent("MonPasaje"))
                    _D_ManifiestoBE.FlgLock = IIf(IsDBNull(drCurrent("FlgLock")), 0, drCurrent("FlgLock"))
                    _D_ManifiestoBE.FlgResponsable = IIf(IsDBNull(drCurrent("FlgResponsable")), False, drCurrent("FlgResponsable"))
                    _D_ManifiestoBE.FlgNoEditar = IIf(IsDBNull(drCurrent("FlgNoEditar")), False, drCurrent("FlgNoEditar"))
                    _D_ManifiestoBE.FlgChecking = IIf(IsDBNull(drCurrent("FlgChecking")), False, drCurrent("FlgChecking"))
                Next
                Return _D_ManifiestoBE
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_RecuperarDetalle) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function


    Public Function pr_ActualizarCabecera(ByVal p_C_Manifiesto As C_ManifiestoBE) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim sInstruccion As String = " UPDATE dbo.C_Manifiesto SET FchViaje = @FchViaje ,CodLugarOrigen = @CodLugarOrigen  ,CodLugarDestino = @CodLugarDestino ,NomAeropuerto = @NomAeropuerto ,CodEmpresa = @CodEmpresa " & _
               ",ObsCabeceraManifiesto = @ObsCabeceraManifiesto ,FlgValija = @FlgValija ,Contacto = @Contacto, ObsCabecera = @ObsCabecera, TelContacto = @TelContacto WHERE CodViaje = @CodViaje "
            Try
                Dim cmd3 As New SqlCommand(sInstruccion, cn)
                Using cmd3
                    cmd3.Parameters.AddWithValue("@CodViaje", p_C_Manifiesto.CodViaje)
                    cmd3.Parameters.AddWithValue("@FchViaje", p_C_Manifiesto.FchViaje)
                    cmd3.Parameters.AddWithValue("@CodLugarOrigen", p_C_Manifiesto.CodLugarOrigen)
                    cmd3.Parameters.AddWithValue("@CodLugarDestino", p_C_Manifiesto.CodLugarDestino)
                    cmd3.Parameters.AddWithValue("@NomAeropuerto", p_C_Manifiesto.NomAeropuerto.Trim)
                    cmd3.Parameters.AddWithValue("@CodEmpresa", p_C_Manifiesto.CodEmpresa)
                    cmd3.Parameters.AddWithValue("@ObsCabeceraManifiesto", p_C_Manifiesto.ObsCabeceraManifiesto.Trim)
                    cmd3.Parameters.AddWithValue("@FlgValija", p_C_Manifiesto.FlgValija)
                    cmd3.Parameters.AddWithValue("@Contacto", p_C_Manifiesto.Contacto.Trim)
                    cmd3.Parameters.AddWithValue("@ObsCabecera", p_C_Manifiesto.ObsCabecera.Trim)
                    cmd3.Parameters.AddWithValue("@TelContacto", p_C_Manifiesto.TelContacto.Trim)
                    cmd3.Transaction = oTran
                    cmd3.ExecuteNonQuery()
                End Using
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_ActualizarCabecera) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function


    Public Function pr_ActualizarEstado(ByVal p_CodViaje As String, ByVal p_EstadoManifiesto As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim sInstruccion As String = " UPDATE dbo.C_Manifiesto SET EstadoManifiesto = @EstadoManifiesto " & _
               " WHERE CodViaje = @CodViaje "
            Try
                Dim cmd3 As New SqlCommand(sInstruccion, cn)
                Using cmd3
                    cmd3.Parameters.AddWithValue("@CodViaje", p_CodViaje)
                    cmd3.Parameters.AddWithValue("@EstadoManifiesto", p_EstadoManifiesto)
                    cmd3.Transaction = oTran
                    cmd3.ExecuteNonQuery()
                End Using
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_ActualizarEstado) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function


    Public Function pr_ActualizarNroValija(ByVal p_CodViaje As String, ByVal p_NroValija As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim sInstruccion As String = " UPDATE dbo.C_Manifiesto SET NroValija = @NroValija " & _
               " WHERE CodViaje = @CodViaje "
            Try
                Dim cmd3 As New SqlCommand(sInstruccion, cn)
                Using cmd3
                    cmd3.Parameters.AddWithValue("@CodViaje", p_CodViaje)
                    cmd3.Parameters.AddWithValue("@NroValija", p_NroValija)
                    cmd3.Transaction = oTran
                    cmd3.ExecuteNonQuery()
                End Using
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_ActualizarNroValija) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function


    Public Function ListarManifiesto() As DataTable
        Dim dts As New DataSet
        Dim ada As SqlDataAdapter
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "sp_ListadeManifiestoCerrado"
            ada = New SqlDataAdapter(cmd)
            ada.Fill(dts, "C_Manifiesto")
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
        Return dts.Tables("C_Manifiesto")
    End Function

    Public Function ListarDetalleManifiesto(ByVal vCodman As String) As DataTable
        Dim dts As New DataSet
        Dim ada As SqlDataAdapter
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "sp_ListaDetalleManifiestoxCodigo"
            cmd.Parameters.Clear()
            cmd.Parameters.Add(New SqlParameter("@CodViaje", SqlDbType.Char, 12))
            cmd.Parameters("@CodViaje").Value = vCodman
            ada = New SqlDataAdapter(cmd)
            ada.Fill(dts, "C_Manifiesto")
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
        Return dts.Tables("C_Manifiesto")
    End Function

    Public Function ListarManifiestoBuscar() As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Listar_Manifiesto_Buscar", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Manifiesto")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ManifiestoADO (ListarManifiestoBuscar) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_RecuperarListaManifiestoImpresion(ByVal p_CodViaje As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Listar_Manifiesto_Impresion", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodViaje", SqlDbType.Char, 12)
                    cmd.Parameters("@p_CodViaje").Value = p_CodViaje.Trim
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Manifiesto")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ManifiestoADO (pr_RecuperarListaManifiestoImpresion) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function


    Public Function pr_UpdateDetalleManifiestoViaje(ByVal p_CodViaje As String, ByVal p_CodReserva As String, ByVal p_CodViajeChange As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim mySqlCommand As SqlCommand
            Dim sInstruccion As String = " Update dbo.D_Manifiesto Set CodViaje = @CodViajeChange  WHERE CodViaje = @CodViaje AND CodReserva = @CodReserva "
            Try
                mySqlCommand = New SqlCommand(sInstruccion, cn)
                mySqlCommand.Transaction = oTran
                mySqlCommand.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                mySqlCommand.Parameters.AddWithValue("@CodReserva", p_CodReserva.Trim)
                mySqlCommand.Parameters.AddWithValue("@CodViajeChange", p_CodViajeChange.Trim)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_UpdateDetalleManifiestoViaje) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function
End Class
